package com.efesco.finance.adjust;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * queryAdjust_adjustDatasetListener
 */
public class queryAdjust_adjustDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
    String fesc_user_id="",sql="",foreign_enterprise_id="";
  	
  	DoradoContext context = DoradoContext.getContext();
    Object obj = context.getAttribute(context.SESSION, "User");
    if (obj != null) {
    	User user = (User) obj;
    	int roleType = user.getRoleType();
    	if (roleType == 2)  {
    		fesc_user_id=user.getForEmployeeId();
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		sql = "SELECT cmp.COMPANY_CHINESE_NAME,o.ought_month,o.ought_amount,to_char(a.modify_date,'yyyy-mm-dd') modify_date,a.modify_amount,a.modify_memo,u1.real_name apply_man,"+
					"a.check_pass,a.check_memo,u2.real_name check_man,to_char(a.verification_time,'yyyy-mm-dd') verification_time,a.actual_receive_adjust_id,o.payment_version_id "+ 
					"from s_ought_receive o,s_actual_receive_adjust a, s_single_company cmp,s_user u1,s_user u2,"+
					"s_company_businessman cbm "+  
					"WHERE " +
					"o.ought_receive_id=a.ought_receive_id and cmp.SINGLE_COMPANY_ID = o.SINGLE_COMPANY_ID AND "+ 
					"cmp.single_company_id = cbm.single_company_id and exists "+ DBTools.getRightSqlByBusinessman(fesc_user_id,"cbm")+
					" and cmp.FOREIGN_ENTERPRISE_ID = " + foreign_enterprise_id  + 
					" and cbm.business_id=1 and a.modify_fesc_employee_id=u1.user_id and a.check_fesc_employee_id=u2.user_id(+) ";
    	}
    	else  {
    		sql = "SELECT cmp.COMPANY_CHINESE_NAME,o.ought_month,o.ought_amount,to_char(a.modify_date,'yyyy-mm-dd') modify_date,a.modify_amount,a.modify_memo,u1.real_name apply_man,"+
	 				"a.check_pass,a.check_memo,u2.real_name check_man,to_char(a.verification_time,'yyyy-mm-dd') verification_time,a.actual_receive_adjust_id,o.payment_version_id "+ 
					"from s_ought_receive o,s_actual_receive_adjust a, s_single_company cmp,s_user u1,s_user u2 "+
					"WHERE " +
					"o.ought_receive_id=a.ought_receive_id and cmp.SINGLE_COMPANY_ID = o.SINGLE_COMPANY_ID AND "+ 
					"a.modify_fesc_employee_id=u1.user_id and a.check_fesc_employee_id=u2.user_id(+) ";
    	}
    	if (!Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("null")	)  {
    		sql = sql + " and cmp.single_company_id = " + dataset.parameters().getString("singleCompanyId");
    	}
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("null")	)  {
        	sql = sql + " and o.ought_month >= '" + dataset.parameters().getString("oughtMonth1")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("")
            && !Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("null")	)  {
            sql = sql + " and o.ought_month <= '" + dataset.parameters().getString("oughtMonth2")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount1")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("amount1")).equals("null")	)  {
            sql = sql + " and a.modify_amount >= " + dataset.parameters().getString("amount1");
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount2")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("amount2")).equals("null")	)  {
            sql = sql + " and a.modify_amount <= " + dataset.parameters().getString("amount2");
        }
    	if (!Tools.toStr(dataset.parameters().getString("businessmanId")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("businessmanId")).equals("null")	)  {
        	sql = sql + " and cbm.businessman_id = " + dataset.parameters().getString("businessmanId");
        }   
    	if (!Tools.toStr(dataset.parameters().getString("date1")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("date1")).equals("null")	)  {
    		String date1=Tools.format(dataset.parameters().getDate("date1"),"yyyy-MM-dd");
            sql = sql + " and a.modify_date >= to_date('" + date1 + "','yyyy-mm-dd')";
        }
        if (!Tools.toStr(dataset.parameters().getString("date2")).equals("")
           	&& !Tools.toStr(dataset.parameters().getString("date2")).equals("null")	)  {
        	String date2=Tools.format(dataset.parameters().getDate("date2"),"yyyy-MM-dd");
        	sql = sql + " and a.modify_date <= to_date('" + date2 + "','yyyy-mm-dd')";
        }
        if (!Tools.toStr(dataset.parameters().getString("checkPass")).equals("")
           	&& !Tools.toStr(dataset.parameters().getString("checkPass")).equals("null")	)  {
            sql = sql + " and a.check_pass = " + dataset.parameters().getString("checkPass");
        }        
    	sql = sql + " order by a.actual_receive_adjust_id desc,cmp.COMPANY_CHINESE_NAME ";
    	SqlDataset sqlDataset=(SqlDataset)dataset;
    	sqlDataset.setSql(sql);
    }
  	
    return true;
  }

}
